package com.las.bot.dao;

import com.las.dao.base.BaseDao;
import com.model.CqMsgUrl;
import com.model.ReWard;
import com.model.SeMember;
import com.model.SeReward;
import com.utils.DateUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.stereotype.Component;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

@Component
public class SeRewardDao extends BaseDao<SeReward> {

    public SeRewardDao() {
        super(SeReward.class);
    }

    /**
     * 根据QQ用户ID查询本周他获得的全部赏金
     *
     * @param uId 用户ID
     * @return
     */
    public List<SeReward> findListByTheWeek(long uId) {
        List<SeReward> list = null;
        String firstWeekByNowTime = DateUtils.getFirstWeekByNowTime();
        Date firstWeekDate = DateUtils.formatToDate(DateUtils.HOUR_24, firstWeekByNowTime);
        Date lastWeekDate = DateUtils.addDay(firstWeekDate, 7);
        String startTime = DateUtils.formatYMD(firstWeekDate) + " 00:00:00";
        String endTime = DateUtils.formatYMD(lastWeekDate) + " 00:00:00";
        String sql = "select sr.id,sr.img_id,sr.rarity_data,sr.se_data,cc.url,sr.create_time,\n" +
                "cast(sr.img_reward/sr.img_probability as decimal(18,2)) as reward\n" +
                "from se_reward sr\n" +
                "inner join cq_msg_url cc on cc.id = sr.img_id\n" +
                "where sr.user_id = ? and sr.rarity_data in('A','S','SS','SSS')\n" +
                "and sr.create_time between ? and ?\n" +
                "order by sr.create_time desc";
        QueryRunner qr = getRunner();
        try {
            list = qr.query(sql, new BeanListHandler<>(SeReward.class, getProcessor()), uId, startTime, endTime);
        } catch (SQLException ignored) {

        }
        return list;
    }

    public List<SeReward> findTop() {
        List<SeReward> list = null;
        String firstWeekByNowTime = DateUtils.getFirstWeekByNowTime();
        Date firstWeekDate = DateUtils.formatToDate(DateUtils.HOUR_24, firstWeekByNowTime);
        Date lastWeekDate = DateUtils.addDay(firstWeekDate, 7);
        String startTime = DateUtils.formatYMD(firstWeekDate) + " 00:00:00";
        String endTime = DateUtils.formatYMD(lastWeekDate) + " 00:00:00";
        String sql = "select temp.user_id,sum(temp.reward) se_data from (\n" +
                "select sr.user_id,sr.id,sr.img_id,sr.rarity_data,sr.se_data,cc.url,sr.create_time,\n" +
                "cast(sr.img_reward/sr.img_probability as decimal(18,2)) as reward\n" +
                "from se_reward sr\n" +
                "inner join cq_msg_url cc on cc.id = sr.img_id\n" +
                "where sr.create_time between ? and ?\n" +
                "order by sr.user_id,sr.create_time desc \n" +
                ") temp\n" +
                "group by temp.user_id having(sum(temp.reward) > 0) \n" +
                "order by se_data desc limit 5";
        QueryRunner qr = getRunner();
        try {
            list = qr.query(sql, new BeanListHandler<>(SeReward.class, getProcessor()), startTime, endTime);
        } catch (SQLException ignored) {

        }
        return list;
    }


}
